package cn.cdl.report.util;


import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtil {

	private static final String PRE_07 = ".xls";
	private static final String SUF_07 = ".xlsx";
	
	private Workbook workbook;
	private Sheet[] sheets ;
	
	/***
	 * 初始化Workbook
	 * @param suffix
	 * @param inputStream
	 * @throws Exception 
	 */
	public ExcelUtil(String fileName,InputStream inputStream) throws Exception{
        String suffix = "" ;
        suffix = fileName.substring(fileName.lastIndexOf("."));
        init(suffix,inputStream);
        
	}
	
	public ExcelUtil(MultipartFile file) throws Exception{
		String fileName = file.getOriginalFilename();
		String suffix = fileName.substring(fileName.lastIndexOf("."));
		InputStream is = new ByteArrayInputStream(file.getBytes());
		init(suffix,is);
	}
	
	public void init(String suffix ,InputStream inputStream) throws Exception{
		if(PRE_07.equals(suffix)){
            //支持07版本以前的excel
       	 workbook= new HSSFWorkbook(inputStream);
        }else if(SUF_07.equals(suffix)){
            //支持07版本以后的excel
       	 workbook = new XSSFWorkbook(inputStream);
        }else{
           System.out.println("不支持的文件类型！");
            return;
       }
       //当前workbook中的总sheet数
       int sn =  workbook.getNumberOfSheets();
       sheets = new Sheet[sn];
       for(int i=0;i<sn;i++){
       	sheets[i] = workbook.getSheetAt(i);
       }

	}
	
	/***
	 * 根据sheet页索引读取数据
	 * @param index
	 * @return
	 */
	public List readSheetByIndex(int index){
		Sheet sheet = sheets[index];
		return readSheet(sheet);
	}
	
	
	/***
	 * 根据sheet页的名字读取数据
	 * @param sheetName
	 * @return
	 */
	public List readSheetByName(String sheetName){
		Sheet sheet = null ;
		for(Sheet sh:sheets){
			if(sheetName.equals(sh.getSheetName())){
				sheet = sh ;
				break;
			}
		}
		return readSheet(sheet);
	}
	
	/***
	 * 
	 * @param sheet
	 */
	public List readSheet(Sheet sheet){
		List<String[]> list = new ArrayList<String[]>();
		//最后一行位置
		int r_num = sheet.getLastRowNum();
		//最后一列位置
		int c_num = sheet.getRow(0).getPhysicalNumberOfCells();
		//循环遍历行
		for(int i = 0;i <= r_num; i++){
			Row row = sheet.getRow(i);
			String[] arrayRow = new String[c_num];
			//轮询一行中的每个单元格内容
			for(int j = 0 ;j < c_num; j++){
				Cell cell = row.getCell(j);
				if(cell == null){
					arrayRow[j] = "" ;
					continue;
				}
				int cellType = cell.getCellType();
				//将单元格数据全部以字符串的形式保存
				switch(cellType){
					case 0 :    //数字格式（日期格式也变归为些类型）
						String value = null ;
						if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是否为日期格式
							Date date = cell.getDateCellValue();
							value = DateUtil.DataToString(date);
						} else {
							value = String.valueOf(cell.getNumericCellValue());
						}
						arrayRow[j] = value;
						break;
					case 1 :    //字符串格式
						arrayRow[j] = cell.getStringCellValue();
						break;
					case 3 :    //其他格式
						arrayRow[j] = "";
						break;
					default :
						arrayRow[j] = "";
				}
			}
			list.add(arrayRow);
		}
		return list;
	}
	
	/***
	 * 获取sheet页数据
	 * @return
	 */
	public int getSheetNum(){
		if(sheets != null){
			return sheets.length;
		}else{
			return 0;
		}
	}
}
